<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of annonceParOrgine
 *
 * @author mssae-9
 */
class annonceParOrigineSrv {
    
    /*
     * Calcule le nombre d'annonce par partenaire et ao
     */
    public static function countAnnonce(){
        
        //$cnx    = jDb::getConnection();
        $result = new stdClass();
        $result->nbPa = self::countAnnoncePerType(0);
        $result->nbAo = self::countAnnoncePerType(2);
        /*
        $sqlPa = "SELECT COUNT(IdOccasion) AS Nb
                    FROM ao_voiture_occasion 
                    WHERE Occas_AffPart = '0' AND Occas_EstSaisie='1'";
        $sqlAo = "SELECT COUNT(IdOccasion) AS Nb
					FROM ao_voiture_occasion 
					WHERE Occas_AffPart = '2' AND Occas_Estsaisie = '1'";
        $rs  = $cnx->query($sqlPa);
        $row = $rs->fetchAll();
        $result->nbPa = $row[0]->Nb;
        
        $rs  = $cnx->query($sqlAo);
        $row = $rs->fetchAll();
        $result->nbAo = $row[0]->Nb;
        
        */
		return $result;
        
    }
    
   
    
    /*
     * Recuperer les annonces des differents partenaires
     */
    public static function listAnnoncefromPartenaire(){
        
        $cnx    = jDb::getConnection();
        
        $sql    = "SELECT IF (Occas_Partenaire <> '',COUNT(IdOccasion),0) AS Nb, Part_Libelle, Occas_Partenaire, IdPartenaire
					FROM ao_partenaire 
                    LEFT OUTER JOIN ao_voiture_occasion ON IdPartenaire = Occas_Partenaire 
					WHERE Occas_Estsaisie = '1'
                    AND Occas_AffPart = '1' 
                    AND (
                     ( Occas_Valide = '0' AND Occas_DateModif = '0000-00-00')
                     OR
                     ( Occas_Valide = '0' AND Occas_DateModif <> '0000-00-00')
                     OR
                     ( Occas_Valide = '1' AND Occas_DateModif <= Occas_DateDerAdmin)
                    )
					GROUP BY IdPartenaire
					ORDER BY Part_Libelle ASC";
        $rs     = $cnx->query($sql);
        $result = $rs->fetchAll();
        

		return $result;
        
    }
    
    
    
    /*
     * detail des annonces 
     */
    public static function detailAnnonce($typeListeDetail,$idPartenaire = '',$_iDebutListe, $_iParPage){
        
        $cnx    = jDb::getConnection();
        $result = new stdClass();
        
        switch ($typeListeDetail) {
            case 'partenaire':
                $result->title2 = "Partenaire : $idPartenaire<br>";
                $result->title1 = "Annonces de partenaires :<br>".$idPartenaire;
                $AffPart = 1;
                $Insert_Query = " AND Occas_Partenaire = '".$idPartenaire."' AND Occas_Estsaisie = '1'";

                break;
            case 'particulier':
                $result->title1 = "Annonces de particuliers<br>";
                $result->title2 = "";
                $AffPart = 0;
                $Insert_Query = " AND Occas_Estsaisie = '1' ";


                break;
            case 'ao':
                $result->title1 = "Annonces d'Auto Occasion<br>";
                $result->title2 = "";
                $AffPart = 2;
                $Insert_Query = " AND Occas_Estsaisie = '1' ";


                break;

            default:
                break;
        }
        
        $sql    = "SELECT IdOccasion, DATE_FORMAT(Occas_DateSaisie,'%d-%m-%Y') AS Occas_DateSaisie,
						CONCAT(SUBSTRING_INDEX(Occas_Reference,'-',1),'-',SUBSTRING_INDEX(Occas_Reference,'-',-1)) AS Occas_Reference, Occas_NumImmatricul,
						DATE_FORMAT(Vendeur_Naissance,'%d-%m-%Y') AS Vendeur_Naissance, IF(Vendeur_Nom='',Part_Libelle,Vendeur_Nom) AS Nom
					FROM ao_voiture_occasion
                    LEFT JOIN ao_vendeur ON Occas_Vendeur = IdVendeur
					LEFT JOIN ao_partenaire ON Occas_Partenaire = IdPartenaire ";
        
        //annonce à valider
        $sqlAValider = $sql . " WHERE Occas_AffPart = '".$AffPart."'".$Insert_Query."
                                AND Occas_Valide = '0'
                                AND Occas_DateModif = '0000-00-00'
                                ORDER BY Occas_Reference DESC";
        $rs     = $cnx->query($sqlAValider);
        $result->AValider = $rs->fetchAll();
        
        
        //annonce modifier a ctrl
        $sqlModif = $sql . " WHERE Occas_AffPart = '".$AffPart."'".$Insert_Query." 
                                AND Occas_Valide = '0'
                                AND Occas_DateModif <> '0000-00-00'
                                ORDER BY Occas_Reference DESC";
        
        $rs     = $cnx->query($sqlModif);
        $result->AModif = $rs->fetchAll();
        
        
        //Les annonces en ligne
        $sqlELigne = $sql . " WHERE Occas_AffPart = '".$AffPart."' ".$Insert_Query."
                                AND Occas_Valide = '1'
                                AND Occas_DateModif <= Occas_DateDerAdmin
                                ORDER BY Occas_Reference DESC";
        $rs     = $cnx->query($sqlELigne);
        $result->ELigne = $rs->fetchAll();
        
        $result->all    = array();
        $result->iV = 'n';
        $result->iM = 'n';
        $result->iL = 'n';
        if(sizeof($result->AValider)){
            $result->all    = array_merge($result->all,$result->AValider);
            $result->iV = '0';
        }
        
        if(sizeof($result->AModif)){
            $result->iM = (sizeof($result->all) > 0) ? sizeof($result->all) : '0';
            $result->all    = array_merge($result->all,$result->AModif);
        }
       
        if(sizeof($result->ELigne)){
            $result->iL = (sizeof($result->all) > 0) ? sizeof($result->all) : '0';
            $result->all    = array_merge($result->all,$result->ELigne);
        }
        $iSize           = sizeof($result->all);
        $result->all     = array_slice($result->all, $_iDebutListe, $_iParPage);
        $result->nbRes   = $iSize;
        $result->iV = ($result->iV != 'n')?($result->iV - $_iDebutListe) : '-9999999991';
        $result->iM = ($result->iM != 'n')?($result->iM - $_iDebutListe) : '-9999999992';
        $result->iL = ($result->iL != 'n')?($result->iL - $_iDebutListe) : '-9999999993';
        
        $result->idP  = $idPartenaire;
        $result->type = $typeListeDetail;
        unset($result->AValider);
        unset($result->AModif);
        unset($result->ELigne);

        
		return $result;
        
    }
    /*
     * fonction pour compter annonce par type
     */
     public static function countAnnoncePerType($AffPart){
        $cnx    = jDb::getConnection();
        $nombre = 0;
        
        $sql    = "SELECT COUNT(IdOccasion) AS nb
					FROM ao_voiture_occasion
                    LEFT JOIN ao_vendeur ON Occas_Vendeur = IdVendeur
					LEFT JOIN ao_partenaire ON Occas_Partenaire = IdPartenaire 
                    WHERE Occas_AffPart = '".$AffPart."' 
                    AND Occas_Estsaisie = '1'
                    AND (
                     ( Occas_Valide = '0' AND Occas_DateModif = '0000-00-00')
                     OR
                     ( Occas_Valide = '0' AND Occas_DateModif <> '0000-00-00')
                     OR
                     ( Occas_Valide = '1' AND Occas_DateModif <= Occas_DateDerAdmin)
                    )";
        $rs      = $cnx->query($sql);
        $res     = $rs->fetchAll();
        $nombre += $res[0]->nb;
        return $nombre;
    }
}

?>
